﻿using System;
using System.Collections.Generic;
using Less.Security.DAL.Records;

namespace Less.Security.DAL
{

    /// <summary>
    /// Data Access Layer to work with Roles Records
    /// </summary>
    public static class UsersRolesDAL
    {

        /// <summary>
        /// <add key="TableName" value="UsersRoles"/>
        /// </summary>
        /// <returns></returns>
        public static string TableName
        {
            get { return Config.GetValue<string>("UsersRolesDAL.TableName", "UsersRoles"); }
            set { Config.SetValue("UsersRolesDAL.TableName", value); }
        }

        /// <summary>
        /// <add key="IdColumnName" value="Id"/>
        /// </summary>
        /// <returns></returns>
        public static string IdColumnName
        {
            get { return Config.GetValue<string>("UsersRolesDAL.IdColumnName", "Id"); }
            set { Config.SetValue("UsersRolesDAL.IdColumnName", value); }
        }

        /// <summary>
        /// <add key="ExistsTable" value="SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'UsersRoles' "/>
        /// </summary>
        /// <returns></returns>
        public static string ExistsTable
        {
            get { return Config.GetValue<string>("UsersRolesDAL.ExistsTable", "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'UsersRoles' "); }
            set { Config.SetValue("UsersRolesDAL.ExistsTable", value); }
        }

        /// <summary>
        /// <add key="CreateTable" value="CREATE TABLE UsersRoles (Id bigint IDENTITY(1,1) PRIMARY KEY,UserId bigint NOT NULL,RoleId bigint NOT NULL)"/>
        /// </summary>
        /// <returns></returns>
        public static string CreateTable
        {
            get
            {
                return Config.GetValue<string>("UsersRolesDAL.CreateTable", @"CREATE TABLE UsersRoles (Id bigint IDENTITY(1,1) PRIMARY KEY,UserId bigint NOT NULL,RoleId bigint NOT NULL)");
            }
            set { Config.SetValue("UsersRolesDAL.CreateTable", value); }
        }

        public static object token = new object();
        private static bool _tableReady = false;
        public static bool TableReady
        {
            get
            {
                lock (token)
                {
                    if (_tableReady == false)
                        _tableReady = PrepareTable();
                    return _tableReady;
                }
            }
            set { _tableReady = value; }
        }

        private static bool PrepareTable()
        {
            bool tableOk = false;
            try
            {
                //Data.Database.ExecuteScalar<object>(string.Format("DROP TABLE {0}", Settings.TableName));
                object executeScalar = Data.Database.ExecuteScalar<object>(ExistsTable);
                if (executeScalar == null)
                {
                    Log.Warning(string.Format("Table {0} Does not exists", TableName), Log.LogEntry.EntryCategories.DataBase);
                    Data.Database.Execute(CreateTable);
                    Log.Info(string.Format("Table {0} Created", TableName), Log.LogEntry.EntryCategories.DataBase);
                }
                else
                {
                    Log.Debug(string.Format("Table {0} Already Exists", TableName), Log.LogEntry.EntryCategories.DataBase);
                }
                tableOk = true;
            }
            catch (Exception exception)
            {
                Log.Error(exception);
            }
            return tableOk;
        }


        /// <summary>
        /// Gets all the User Records
        /// </summary>
        /// <returns></returns>
        public static List<UserRoleRecord> SelectAll()
        {
            List<UserRoleRecord> list = null;
            if (TableReady)
                list = Data.Database.Fetch<UserRoleRecord>(string.Format("SELECT * FROM {0}", TableName));
            return list;
        }

        /// <summary>
        /// Gets all the User Records
        /// </summary>
        /// <returns></returns>
        public static List<UserRoleRecord> SelectAll(int pageIndex, int pageSize, out int totalRecords)
        {
            List<UserRoleRecord> list = null;
            totalRecords = 0;
            if (TableReady)
            {
                var page = Data.Database.Page<UserRoleRecord>(pageIndex, pageSize, (string.Format("SELECT * FROM {0}", TableName)));
                list = page.Items;
                totalRecords = (int)page.TotalItems;
            }
            return list;
        }




        /// <summary>
        /// Gets a UserRole Record 
        /// </summary>
        /// <param name="id">The UserRole Id</param>
        /// <returns>The UserRole Record </returns>
        public static UserRoleRecord SelectById(long id)
        {
            UserRoleRecord record = null;
            if (TableReady)
                record = Data.Database.SingleOrDefault<UserRoleRecord>(string.Format("SELECT * FROM {0} WHERE Id=@0", TableName), id);
            return record;
        }


        /// <summary>
        /// Gets a UserRole Record 
        /// </summary>
        /// <param name="userId">The User Id</param>
        /// <param name="roleId">The Role Id</param>
        /// <returns>The UserRole Record </returns>
        public static UserRoleRecord SelectByUserIdRoleId(long userId, long roleId)
        {
            UserRoleRecord record = null;
            if (TableReady)
                record = Data.Database.SingleOrDefault<UserRoleRecord>(string.Format("SELECT * FROM {0} WHERE UserId=@0 ANd RoleId=@1", TableName), userId, roleId);
            return record;
        }

        /// <summary>
        /// Inserts a User Record 
        /// </summary>
        /// <param name="record">The User Record</param>
        public static void Insert(UserRoleRecord record)
        {
            // TODO: Check if this record already exists
            if (TableReady)
                Data.Database.Insert(TableName, IdColumnName, record);
        }

        /// <summary>
        /// Updates a User Record 
        ///  </summary>
        /// <param name="record">The User Record and its associated Entity Record</param>
        public static void Update(UserRoleRecord record)
        {
            if (TableReady)
                Data.Database.Update(TableName, IdColumnName, record);
        }

        /// <summary>
        /// Updates a User Record 
        ///  </summary>
        /// <param name="record">The User Record and its associated Entity Record</param>
        public static void Delete(UserRoleRecord record)
        {
            if (TableReady)
                Data.Database.Delete(TableName, IdColumnName, record);
        }

        public static bool IsUserInRole(string username, string roleName)
        {
            bool exists = false;
            // OPTI: use an INNER JOIN
            UserRecord user = UsersDAL.SelectByName(username);
            if (user != null)
            {
                RoleRecord role = RolesDAL.SelectByName(roleName);
                if (role != null)
                    if (TableReady)
                        exists = Data.Database.ExecuteScalar<int>(string.Format("SELECT Count(*) FROM {0} WHERE UserId=@0 AND RoleId=@1", TableName), user.Id, role.Id) > 0;
            }
            return exists;
        }



        public static void AddUsersToRoles(string[] usernames, string[] roleNames)
        {
            // OPTI: use an inner join 
            foreach (string roleName in roleNames)
            {
                RoleRecord role = RolesDAL.SelectByName(roleName);
                if (role != null)
                {
                    foreach (string username in usernames)
                    {
                        UserRecord user = UsersDAL.SelectByName(username);
                        if (user != null)
                        {
                            UserRoleRecord userRole = new UserRoleRecord() { UserId = user.Id, RoleId = role.Id };
                            Insert(userRole);
                        }
                        else
                        {
                            Log.Warning(string.Format("User {0} not found", username));
                        }
                    }
                }
                else
                {
                    Log.Warning(string.Format("Role {0} not found", roleName));
                }
            }
        }

        public static void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
        {
            // OPTI: use an inner join 
            foreach (string roleName in roleNames)
            {
                RoleRecord role = RolesDAL.SelectByName(roleName);
                if (role != null)
                {
                    foreach (string username in usernames)
                    {
                        UserRecord user = UsersDAL.SelectByName(username);
                        if (user != null)
                        {
                            UserRoleRecord userRole = SelectByUserIdRoleId(user.Id, role.Id);
                            Delete(userRole);
                        }
                        else
                        {
                            Log.Warning(string.Format("User {0} not found", username));
                        }
                    }
                }
                else
                {
                    Log.Warning(string.Format("Role {0} not found", roleName));
                }
            }
        }
    }
}
